package com.ecommerce.dao;

import com.ecommerce.model.InventoryHistory;
import com.ecommerce.model.Product;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProductDAO {

    // 获取所有上架商品（顾客使用）
    public List<Product> getActiveProducts() {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT p.*, c.name as category_name FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "WHERE p.status = 'active' ORDER BY p.name";

        try (Connection conn = DatabaseConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                products.add(extractProductFromResultSet(rs));
            }

        } catch (SQLException e) {
            System.out.println("获取商品列表失败: " + e.getMessage());
        }

        return products;
    }

    // 获取所有商品（管理员使用）
    public List<Product> getAllProducts() {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT p.*, c.name as category_name FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "ORDER BY p.status, p.name";

        try (Connection conn = DatabaseConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                products.add(extractProductFromResultSet(rs));
            }

        } catch (SQLException e) {
            System.out.println("获取商品列表失败: " + e.getMessage());
        }

        return products;
    }

    public Product getProductById(int id) {
        String sql = "SELECT p.*, c.name as category_name FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id WHERE p.id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return extractProductFromResultSet(rs);
            }

        } catch (SQLException e) {
            System.out.println("获取商品失败: " + e.getMessage());
        }

        return null;
    }

    // 顾客只能获取上架的商品详情
    public Product getActiveProductById(int id) {
        String sql = "SELECT p.*, c.name as category_name FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "WHERE p.id = ? AND p.status = 'active'";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, id);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return extractProductFromResultSet(rs);
            }

        } catch (SQLException e) {
            System.out.println("获取商品失败: " + e.getMessage());
        }

        return null;
    }

    public boolean addProduct(Product product) {
        String sql = "INSERT INTO products (name, description, price, stock_quantity, category_id) VALUES (?, ?, ?, ?, ?)";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, product.getName());
            stmt.setString(2, product.getDescription());
            stmt.setDouble(3, product.getPrice());
            stmt.setInt(4, product.getStockQuantity());
            stmt.setInt(5, product.getCategoryId());

            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;

        } catch (SQLException e) {
            System.out.println("添加商品失败: " + e.getMessage());
            return false;
        }
    }

    public boolean updateProduct(Product product) {
        String sql = "UPDATE products SET name = ?, description = ?, price = ?, stock_quantity = ?, category_id = ? WHERE id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, product.getName());
            stmt.setString(2, product.getDescription());
            stmt.setDouble(3, product.getPrice());
            stmt.setInt(4, product.getStockQuantity());
            stmt.setInt(5, product.getCategoryId());
            stmt.setInt(6, product.getId()); // WHERE条件

            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;

        } catch (SQLException e) {
            System.out.println("更新商品失败: " + e.getMessage());
            return false;
        }
    }

    // 修复：商品上下架控制 - 确保有WHERE条件
    public boolean toggleProductStatus(int id) {
        String sql = "UPDATE products SET status = IF(status = 'active', 'inactive', 'active') WHERE id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, id); // WHERE条件
            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;

        } catch (SQLException e) {
            System.out.println("更新商品状态失败: " + e.getMessage());
            return false;
        }
    }

    // 修复：彻底删除商品 - 确保有WHERE条件
    public boolean deleteProductPermanently(int id) {
        String deleteSql = "DELETE FROM products WHERE id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement deleteStmt = conn.prepareStatement(deleteSql)) {

            deleteStmt.setInt(1, id); // WHERE条件
            int rowsAffected = deleteStmt.executeUpdate();
            return rowsAffected > 0;

        } catch (SQLException e) {
            System.out.println("删除商品失败: " + e.getMessage());
            return false;
        }
    }

    public List<Product> getProductsByCategory(int categoryId, boolean includeInactive) {
        List<Product> products = new ArrayList<>();
        String sql;

        if (includeInactive) {
            // 管理员查看所有商品
            sql = "SELECT p.*, c.name as category_name FROM products p " +
                    "LEFT JOIN categories c ON p.category_id = c.id " +
                    "WHERE p.category_id = ? ORDER BY p.status, p.name";
        } else {
            // 顾客只查看上架商品
            sql = "SELECT p.*, c.name as category_name FROM products p " +
                    "LEFT JOIN categories c ON p.category_id = c.id " +
                    "WHERE p.category_id = ? AND p.status = 'active' ORDER BY p.name";
        }

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, categoryId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                products.add(extractProductFromResultSet(rs));
            }

        } catch (SQLException e) {
            System.out.println("按分类获取商品失败: " + e.getMessage());
        }

        return products;
    }

    private Product extractProductFromResultSet(ResultSet rs) throws SQLException {
        Product product = new Product();
        product.setId(rs.getInt("id"));
        product.setName(rs.getString("name"));
        product.setDescription(rs.getString("description"));
        product.setPrice(rs.getDouble("price"));
        product.setStockQuantity(rs.getInt("stock_quantity"));
        product.setLowStockThreshold(rs.getInt("low_stock_threshold"));
        product.setCategoryId(rs.getInt("category_id"));
        product.setCategoryName(rs.getString("category_name"));
        product.setStatus(rs.getString("status"));
        return product;
    }
    // 新增方法：获取低库存商品
    public List<Product> getLowStockProducts() {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT p.*, c.name as category_name FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "WHERE p.stock_quantity <= p.low_stock_threshold AND p.status = 'active' " +
                "ORDER BY p.stock_quantity ASC";

        try (Connection conn = DatabaseConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                products.add(extractProductFromResultSet(rs));
            }

        } catch (SQLException e) {
            System.out.println("获取低库存商品失败: " + e.getMessage());
        }

        return products;
    }

    // 新增方法：更新商品库存（带历史记录）
    public boolean updateProductStock(int productId, int newStock, String changeType,
                                      String reason, int createdBy) {
        Connection conn = null;
        int retryCount = 0;
        final int MAX_RETRIES = 3;

        while (retryCount < MAX_RETRIES) {
            try {
                conn = DatabaseConnection.getConnection();
                conn.setAutoCommit(false);

                // 设置事务隔离级别为读已提交，减少锁竞争
                conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

                // 获取当前库存 - 使用 FOR UPDATE 锁定记录
                String selectSql = "SELECT stock_quantity, low_stock_threshold FROM products WHERE id = ? FOR UPDATE";
                PreparedStatement selectStmt = conn.prepareStatement(selectSql);
                selectStmt.setInt(1, productId);
                ResultSet rs = selectStmt.executeQuery();

                if (!rs.next()) {
                    conn.rollback();
                    System.out.println("商品不存在！");
                    return false;
                }

                int previousStock = rs.getInt("stock_quantity");
                int changeQuantity = newStock - previousStock;

                // 立即关闭结果集和语句，释放资源
                rs.close();
                selectStmt.close();

                // 更新库存
                String updateSql = "UPDATE products SET stock_quantity = ? WHERE id = ?";
                PreparedStatement updateStmt = conn.prepareStatement(updateSql);
                updateStmt.setInt(1, newStock);
                updateStmt.setInt(2, productId);

                int rowsAffected = updateStmt.executeUpdate();
                updateStmt.close();

                if (rowsAffected == 0) {
                    conn.rollback();
                    System.out.println("更新库存失败，商品可能不存在！");
                    return false;
                }

                // 记录库存变更历史 - 使用单独的连接避免死锁
                boolean historyRecorded = recordInventoryChangeInNewConnection(
                        productId, changeType, changeQuantity, previousStock, newStock, reason, createdBy);

                if (!historyRecorded) {
                    conn.rollback();
                    System.out.println("记录库存历史失败！");
                    return false;
                }

                conn.commit();
                return true;

            } catch (SQLException e) {
                if (conn != null) {
                    try {
                        conn.rollback();
                    } catch (SQLException ex) {
                        System.out.println("回滚事务失败: " + ex.getMessage());
                    }
                }

                if (e.getMessage().contains("Lock wait timeout") && retryCount < MAX_RETRIES - 1) {
                    retryCount++;
                    System.out.println("检测到锁等待超时，第 " + retryCount + " 次重试...");
                    try {
                        Thread.sleep(100 * retryCount); // 指数退避
                    } catch (InterruptedException ie) {
                        Thread.currentThread().interrupt();
                    }
                    continue;
                }

                System.out.println("更新商品库存失败: " + e.getMessage());
                return false;
            } finally {
                if (conn != null) {
                    try {
                        conn.setAutoCommit(true);
                        DatabaseConnection.releaseConnection(conn); // 释放到连接池
                    } catch (SQLException e) {
                        System.out.println("关闭连接失败: " + e.getMessage());
                    }
                }
            }
        }
        return false;
    }

    // 使用新连接记录库存历史，避免死锁
    private boolean recordInventoryChangeInNewConnection(int productId, String changeType,
                                                         int changeQuantity, int previousStock,
                                                         int newStock, String reason, int createdBy) {
        try (Connection historyConn = DatabaseConnection.getConnection()) {
            historyConn.setAutoCommit(true); // 自动提交，避免长事务

            InventoryHistory history = new InventoryHistory(productId, changeType,
                    changeQuantity, previousStock, newStock, reason, createdBy);

            InventoryDAO inventoryDAO = new InventoryDAO();
            return inventoryDAO.recordInventoryChange(history);

        } catch (SQLException e) {
            System.out.println("记录库存历史失败: " + e.getMessage());
            return false;
        }
    }
}